﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
namespace j1
{
    public class StudentDataAccess
    {
        public static void AddStudent(Student student)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "INSERT INTO Student (Name, ClassId) VALUES (@name, @classId)";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", student.Name);
                    command.Parameters.AddWithValue("@classId", student.ClassId);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Added Student: {student.Name} to ClassId: {student.ClassId}");
        }

        public static void DeleteStudent(int studentId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "DELETE FROM Student WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", studentId);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Deleted Student with Id: {studentId}");
        }

        public static void UpdateStudent(Student student)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "UPDATE Student SET Name = @name, ClassId = @classId WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", student.Name);
                    command.Parameters.AddWithValue("@classId", student.ClassId);
                    command.Parameters.AddWithValue("@id", student.Id);
                    command.ExecuteNonQuery();
                }
            }
            Database.LogAction($"Updated Student with Id: {student.Id}");
        }

        public static Student GetStudent(int studentId)
        {
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM Student WHERE Id = @id";
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@id", studentId);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                ClassId = Convert.ToInt32(reader["ClassId"])
                            };
                        }
                    }
                }
            }
            return null;
        }

        public static List<Student> GetAllStudents()
        {
            var students = new List<Student>();
            using (var connection = Database.GetConnection())
            {
                connection.Open();
                string query = "SELECT * FROM Student";
                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                ClassId = Convert.ToInt32(reader["ClassId"])
                            });
                        }
                    }
                }
            }
            return students;
        }
    }

}
